package com.smartodo.utils;

import java.sql.*;
import java.util.ArrayList;

import com.smartodo.vo.CreateAccountVO;
import com.smartodo.vo.LoginVO;



public class DbUtils
{
    public static Connection _dbConnection = null;
    
   /*******************************************************************************
    * Function Name:   getConnection
    * Description:     get the database connection
    * Return:          
    *******************************************************************************/
    public static Connection getConnection() 
    {
        if(DbUtils._dbConnection == null)
        {
       		DbUtils.Connect();
        }
        return DbUtils._dbConnection;
    }

    /*******************************************************************************
    * Function Name:    Connect
    * Description:      Connect to the MySQL database
    * Return:           boolean
    *******************************************************************************/
    public static boolean Connect()
    {
	try
	{
    	/* Create string of connection url within specified format with machine name, 
    	port number and database name. Here machine name id localhost and database name 
    	is smartodo. */ 
    	String connectionURL = "jdbc:mysql://localhost/smartodo";

    	// declare a connection by using Connection interface 
    	//Connection connection = null; 

    	// Load JBBC driver "com.mysql.jdbc.Driver". 
    	Class.forName("com.mysql.jdbc.Driver").newInstance(); 

    	/* Create a connection by using getConnection() method that takes parameters of 
    	string type connection url, user name and password to connect to database. */ 
    	DbUtils._dbConnection = DriverManager.getConnection(connectionURL, "smartodo", "smartodo");
    	
	} catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    System.out.println("DbConnection UnSuccessful");
		}
	catch(Exception ex){}
        return true;
    }

   /*******************************************************************************
    * Function Name:    Release
    * Description:      Release resources
    * Return:           void
    *******************************************************************************/
    public static void Release()
    {
        if(DbUtils._dbConnection == null)
            return;
        try {
        	DbUtils._dbConnection.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    /*******************************************************************************
     * Function Name:    InsertData
     * Description:      Insert the record to the database table
     * Return:           boolean
     *******************************************************************************/
     public static boolean InsertTodoData(String loginId, String taskname, String priority, int day, int month, int year)
     throws Exception
     {
    	 DbUtils.getConnection();
         PreparedStatement s = DbUtils._dbConnection.prepareStatement("insert into stodo_task_list(login_id, task_name, priority, task_datetime) values(?,?,?,?)");
         
         s.setString(1, loginId);
         
         s.setString(2, taskname);

         s.setString(3, priority);
         
         //java.sql.Date time = new java.sql.Timestamp(year, month, day,0,0,0,0);
         
         s.setDate(4, new Date((year - 1900), month, day));
         
         System.out.println(s.toString());
         s.executeUpdate();
         s.close();
         return true;
     }
     /*******************************************************************************
      * Function Name:    UpdateTodoData
      * Description:      update the record to the database table
      * Return:           boolean
      *******************************************************************************/
      public static boolean UpdateTodoData(String loginId, int taskid, String taskname, String priority, int day, int month, int year)
      throws Exception
      {
     	 DbUtils.getConnection();
          PreparedStatement s = DbUtils._dbConnection.prepareStatement("update stodo_task_list set task_name = (?), priority = (?), task_datetime = (?) where login_id=(?) and taskid = (?)");
          
          s.setString(1, taskname);

          s.setString(2, priority);
          
          s.setDate(3, new Date((year - 1900), month, day));
          
          s.setString(4, loginId);
          
          s.setInt(5, taskid);

          System.out.println(s.toString());
          s.executeUpdate();
          s.close();
          return true;
      }
      
      /*******************************************************************************
       * Function Name:    DeleteTodoData
       * Description:      delete the record to the database table
       * Return:           boolean
       *******************************************************************************/
       public static boolean DeleteTodoData(String loginId, int taskid)
       throws Exception
       {
      	 DbUtils.getConnection();
           PreparedStatement s = DbUtils._dbConnection.prepareStatement("delete from stodo_task_list where login_id = (?) and taskid = (?)");
           
           s.setString(1, loginId);
           s.setInt(2, taskid);

           System.out.println(s.toString());
           s.executeUpdate();
           s.close();
           return true;
       }
       
     public static ArrayList<STask> getTaskForUser(String loginId, int day, int month, int year)
     {
    	 ArrayList<STask> list = new ArrayList<STask>();
         
         try
         {
        	 DbUtils.getConnection();
             PreparedStatement stmt = _dbConnection.prepareStatement("SELECT taskid, login_id, task_name, priority, task_datetime FROM stodo_task_list WHERE login_id =(?) and task_datetime = (?)",
            		 				ResultSet.TYPE_SCROLL_SENSITIVE,
            		 				ResultSet.CONCUR_READ_ONLY);
             stmt.setString(1, loginId);
             stmt.setDate(2, new Date((year - 1900), (month -1), day));
             System.out.println(stmt.toString());
             ResultSet srs = stmt.executeQuery();
             
             while(srs.next())
             {
            	 STask task = new STask();
            	 task.taskid = srs.getInt("taskid");
            	 task.loginid = srs.getString("login_id");
                 task.task_name = srs.getString("task_name");
            	 task.priority = srs.getString("priority");
            	 task.datetime = srs.getDate("task_datetime");
            	 list.add(task);
             }
             stmt.close();
         }
         catch(Exception ex)
         {
             System.out.println("Error: " + ex);
         }
         return list;
     }
     
     public static STask getTaskById(int taskId)
     {
    	 STask task = null;
         try
         {
        	 DbUtils.getConnection();
             PreparedStatement stmt = _dbConnection.prepareStatement("SELECT taskid, login_id, task_name, priority, task_datetime FROM stodo_task_list WHERE taskid =(?)",
            		 				ResultSet.TYPE_SCROLL_SENSITIVE,
            		 				ResultSet.CONCUR_READ_ONLY);
             stmt.setInt(1, taskId);
             System.out.println(stmt.toString());
             ResultSet srs = stmt.executeQuery();
             
             if(srs.next())
             {
            	 task = new STask();
            	 task.taskid = srs.getInt("taskid");
            	 task.loginid = srs.getString("login_id");
                 task.task_name = srs.getString("task_name");
            	 task.priority = srs.getString("priority");
            	 task.datetime = srs.getDate("task_datetime");
             }
             stmt.close();
         }
         catch(Exception ex)
         {
             System.out.println("Error: " + ex);
         }
         return task;    	 
     }

	public static boolean addUser(CreateAccountVO vo) {

		DbUtils.getConnection();
	    PreparedStatement stmt;
		try {
			stmt = DbUtils._dbConnection.prepareStatement("insert into stodo_user_info(login_id, fname, lname, pwd, security_question, security_answer, location) values(?,?,?,?,?,?,?)");

			stmt.setString(1, vo.getLoginId());
		    stmt.setString(2, vo.getFname());
		    stmt.setString(3, vo.getLname());
		    stmt.setString(4, vo.getPassword());
		    stmt.setString(5, vo.getSecQuestion());
		    stmt.setString(6, vo.getSecAnswere());
		    stmt.setString(7, vo.getLocation());
		    
		    System.out.println(stmt.toString());
		    
		    stmt.executeUpdate();
		    stmt.close();
		    
		} catch (SQLException e) {
			return false;
		}
	    
	    return true;
	}

	public static boolean getUserDetails(LoginVO vo) {
		
		DbUtils.getConnection();
        PreparedStatement stmt;
		
		try {
		
			stmt = _dbConnection.prepareStatement("SELECT pwd FROM stodo_user_info WHERE login_id =(?)",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			stmt.setString(1, vo.getLoginId());
        
			System.out.println(stmt.toString());
            
			ResultSet rs = stmt.executeQuery();
            rs.next();
			String password = rs.getString("pwd");
			
            vo.setPassword(password);
            
            stmt.close();
		
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
}